﻿using Hr.IDAL;
using Hr.Model;
using System;
using System.Collections.Generic;
using System.Data.Common.CommandTrees;
using System.Data.Entity;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.Entity.Migrations;
using System.Linq;
using System.Net.Http.Headers;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;

namespace Hr.DAL
{
    public class FinanceDAL
    {
        // 创建EF对象
        private DbContext db = DbContextFactory.Create();

        //核心dal层代码
        /// <summary>
        /// 获取所有的账号管理
        /// </summary>
        /// <returns></returns>
        public IEnumerable<object> GetMailAccount()
        {
            return db.Set<Finance_Account>().OrderByDescending(n => n.pk_Account).Select(s => new
            {
                s.pk_Account,
                s.TypeID,
                s.Title,
                s.AccountName,
                s.Owner,
                s.CreateDate,
                s.StatusID,
                db.Set<Sys_TypeID>().Where(w => w.TableName.Equals("Finance_Account") & w.ID == s.TypeID).FirstOrDefault().TypeName_CHS,
                db.Set<Sys_StatusID>().Where(w => w.TableName.Equals("Finance_Account") & w.ID == s.StatusID).FirstOrDefault().StatusName_CHS,
                db.Set<Sys_StatusID>().Where(w => w.TableName.Equals("Finance_Account") & w.ID == s.StatusID).FirstOrDefault().Visual,
            }).ToList();
        }
        /// <summary>
        /// 获取所有的财务的管理
        /// </summary>
        /// <returns></returns>
        public IEnumerable<object> GetMailList()
        {
            return db.Set<Finance_List>().OrderByDescending(n => n.pk_Finance).Select(s => new
            {
                s.pk_Finance,
                s.TypeID,
                s.Title,
                s.HandleDate,
                s.HandleName,
                s.fk_Department,
                s.Amount,
                s.StatusID,
                s.FilePath,
                db.Set<Sys_TypeID>().Where(w => w.TableName.Equals("Finance_List") & w.ID == s.TypeID).FirstOrDefault().TypeName_CHS,
                db.Set<Sys_StatusID>().Where(w => w.TableName.Equals("Finance_List") & w.ID == s.StatusID).FirstOrDefault().StatusName_CHS,
                db.Set<Sys_StatusID>().Where(w => w.TableName.Equals("Finance_List") & w.ID == s.StatusID).FirstOrDefault().Visual,
                db.Set<User_Department>().Where(w => w.pk_Department == s.fk_Department).FirstOrDefault().DepartmentName,
                AccountTitle = db.Set<Finance_Account>().Where(w => w.pk_Account == s.fk_Account).FirstOrDefault().Title,
            }).ToList();
        }
        /// <summary>
        /// 根据账号ID，获取账号信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Finance_Account GetFinanceInfo(int id)
        {
            return db.Set<Finance_Account>().SingleOrDefault(n => n.pk_Account == id);
        }
        /// <summary>
        /// 根据财务ID，获取财务信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Finance_List GetFinance(int id)
        {
            return db.Set<Finance_List>().SingleOrDefault(n => n.pk_Finance == id);
        }
        /// <summary>
        /// 根据财务ID，获取财务信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Finance_List GetFinancelisnInfo(int id)
        {
            return db.Set<Finance_List>().SingleOrDefault(n => n.pk_Finance == id);
        }
        /// <summary>
        ///通过表名获取类型Finance_Account
        /// </summary>
        /// <returns></returns>
        public List<Sys_TypeID> GetTypeList(string TableName)
        {
            return db.Set<Sys_TypeID>().Where(n => n.TableName.Equals(TableName)).ToList();
        }
        /// <summary>
        ///通过表名获取状态Finance_Account
        /// </summary>
        /// <returns></returns>
        public List<Sys_StatusID> GetStatusList(string TableName)
        {
            return db.Set<Sys_StatusID>().Where(n => n.TableName.Equals(TableName)).ToList();
        }
        /// <summary>
        ///通过表名获取类型Finance_List
        /// </summary>
        /// <returns></returns>
        public List<Sys_TypeID> GetTypeList2(string TableName)
        {
            return db.Set<Sys_TypeID>().Where(n => n.TableName.Equals(TableName)).ToList();
        }
        /// <summary>
        ///通过表名获取状态Finance_List
        /// </summary>
        /// <returns></returns>
        public List<Sys_StatusID> GetStatusList2(string TableName)
        {
            return db.Set<Sys_StatusID>().Where(n => n.TableName.Equals(TableName)).ToList();
        }
        /// <summary>
        ///通过表名获取部门Finance_List
        /// </summary>
        /// <returns></returns>
        public List<User_Department> GetTypeList3()
        {
            return db.Set<User_Department>().ToList();
        }
        /// <summary>
        ///通过表名获取账号Finance_List
        /// </summary>
        /// <returns></returns>
        public IEnumerable<object> GetStatusList3()
        {
            return db.Set<Finance_Account>().Where(n=>n.StatusID==0).Select(n=>new { n.pk_Account,n.AccountName}).ToList();
        }
        /// <summary>
        /// 账号管理的增加，编辑
        /// </summary>
        /// <returns></returns>
        public void UpdateFinance(Finance_Account r, string type,int pk_User)
        {
            if (type.Equals("Add"))
            {
                r.CreateDate = DateTime.Now;//创建时间就为当前时间
                r.fk_User = pk_User;
                r.fk_Department = db.Set<User_List>().SingleOrDefault(n => n.pk_User == pk_User).fk_Department;
                r.CreateUser = pk_User;
                db.Set<Finance_Account>().Add(r);
            }
            else if (type.Equals("Edit"))
            {
                r.ModifyUser = pk_User;
                r.ModifyDate = DateTime.Now;
                db.Set<Finance_Account>().AddOrUpdate(r);
            }
        }
        /// <summary>
        /// 财务管理的增加，编辑
        /// </summary>
        /// <returns></returns>
        public void UpdateFinancelin(Finance_List r, string type,int pk_User)
        {
            if (type.Equals("Add"))
            {
                r.CreateDate = DateTime.Now;//创建时间就为当前时间
                r.CreateUser = pk_User;
                r.fk_User = pk_User;
                r.fk_Department = db.Set<User_List>().SingleOrDefault(n => n.pk_User == pk_User).fk_Department;
                db.Set<Finance_List>().Add(r);
            }
            else if (type.Equals("Edit"))
            {
                r.ModifyDate = DateTime.Now;
                r.ModifyUser = pk_User;
                db.Set<Finance_List>().AddOrUpdate(r);
            }
        }
        /// <summary>
        /// 根据FullName查询Finance_Account表数据
        /// </summary>
        /// <returns></returns>
        public IEnumerable<object> FNameSelectFinanceList(string AccountName)
        {
            return db.Set<Finance_Account>().OrderByDescending(n => n.pk_Account).Where(n => n.AccountName.Contains(AccountName)).Select(s => new
            {
                s.pk_Account,
                s.TypeID,
                s.Title,
                s.AccountName,
                s.Owner,
                s.CreateDate,
                s.StatusID,
                db.Set<Sys_TypeID>().Where(w => w.TableName.Equals("Finance_Account") & w.ID == s.TypeID).FirstOrDefault().TypeName_CHS,
                db.Set<Sys_StatusID>().Where(w => w.TableName.Equals("Finance_Account") & w.ID == s.StatusID).FirstOrDefault().StatusName_CHS
            }).ToList();
        }
        /// <summary>
        /// 根据FullName查询Finance_List表数据
        /// </summary>
        /// <returns></returns>
        public IEnumerable<object> FNameSelectFinance(string Title)
        {
            return db.Set<Finance_List>().OrderByDescending(n => n.pk_Finance).Where(n => n.Title.Contains(Title)).Select(s => new
            {
                s.pk_Finance,
                s.TypeID,
                s.Title,
                s.HandleDate,
                s.HandleName,
                s.fk_Department,
                s.Amount,
                s.StatusID,
                s.FilePath,
                db.Set<Sys_TypeID>().Where(w => w.TableName.Equals("Finance_List") & w.ID == s.TypeID).FirstOrDefault().TypeName_CHS,
                db.Set<Sys_StatusID>().Where(w => w.TableName.Equals("Finance_List") & w.ID == s.StatusID).FirstOrDefault().StatusName_CHS,
                db.Set<User_Department>().Where(w => w.pk_Department == s.fk_Department).FirstOrDefault().DepartmentName,
                AccountTitle = db.Set<Finance_Account>().Where(w => w.pk_Account == s.fk_Account).FirstOrDefault().Title,
            }).ToList();
        }
        /// <summary>
        /// 删除账号信息
        /// </summary>
        /// <param name="r"></param>
        public void DelAccountdata(Finance_Account r)
        {
            db.Set<Finance_Account>().Remove(r);
        }
        /// <summary>
        /// 删除财务信息
        /// </summary>
        /// <param name="r"></param>
        public void DelFinancedata(Finance_List r)
        {
            db.Set<Finance_List>().Remove(r);
        }
        /// <summary>
        /// 提交
        /// </summary>
        /// <returns></returns>
        public bool SaveChanges()
        {
            return db.SaveChanges() > 0;
        }

        #region  财务报表
        /// <summary>
        /// 收入
        /// </summary>
        /// <returns></returns>
        public IEnumerable<object> FinanceIncome()
        {
            return (from FinanceList in db.Set<Finance_List>()
                    join SysTypeID in db.Set<Sys_TypeID>() on FinanceList.TypeID equals SysTypeID.ID
                    join SysStatusID in db.Set<Sys_StatusID>() on FinanceList.StatusID equals SysStatusID.ID
                    where SysTypeID.TableName.Equals("Finance_List") && SysStatusID.TableName.Equals("Finance_List") && SysTypeID.ID == 0 && SysStatusID.ID == 1
                    select new
                    {
                        FinanceList.Amount
                    }).ToList();
        }

        /// <summary>
        /// 支出
        /// </summary>
        /// <returns></returns>
        public IEnumerable<object> FinancePay()
        {
            return (from FinanceList in db.Set<Finance_List>()
                    join SysTypeID in db.Set<Sys_TypeID>() on FinanceList.TypeID equals SysTypeID.ID
                    join SysStatusID in db.Set<Sys_StatusID>() on FinanceList.StatusID equals SysStatusID.ID
                    where SysTypeID.TableName.Equals("Finance_List") && SysStatusID.TableName.Equals("Finance_List") && SysTypeID.ID == 1 && SysStatusID.ID == 1
                    select new
                    {
                        FinanceList.Amount
                    }).ToList();
        }
        /// <summary>
        /// 待定
        /// </summary>
        /// <returns></returns>
        public IEnumerable<object> Financeindeterminate()
        {
            return (from FinanceList in db.Set<Finance_List>()
                    join SysStatusID in db.Set<Sys_StatusID>() on FinanceList.StatusID equals SysStatusID.ID
                    where SysStatusID.TableName.Equals("Finance_List") && SysStatusID.ID == 0
                    select new
                    {
                        FinanceList.Amount
                    }).ToList();
        }

        /// <summary>
        /// 获取当年当月的盈利
        /// </summary>
        /// <returns></returns>
        public List<Dictionary<string, string>> GetFinceInfo()
        {
            List<Dictionary<string, string>> FinceList = new List<Dictionary<string, string>>();
            //获取当年的所有数据，包括当年12个月
            Finance_List w = db.Set<Finance_List>().First();
            List<Finance_List> FinanceList = db.Set<Finance_List>().Where(n => n.TypeID == 0 && n.StatusID == 1).ToList();
            for (int i = 1; i <= 12; i++)
            {
                double PriceCount = 0;//本月所有员工总工资
                //循环查找通过月份查找
                foreach (Finance_List item in FinanceList)
                {
                    int M = Convert.ToDateTime(item.HandleDate.ToString().Split(' ')[0]).Month;//获取员工工资的月份
                    int Y = Convert.ToDateTime(item.HandleDate.ToString().Split(' ')[0]).Year;
                    if (Y == DateTime.Now.Year && M == i)
                    {
                        PriceCount += double.Parse(item.Amount.ToString());
                    }
                }
                //格式化
                Dictionary<string, string> dt = new Dictionary<string, string>()
                {
                    {"时间",i+"月" },
                    {"盈利",PriceCount.ToString()}
                };
                FinceList.Add(dt);
            }
            return FinceList;
        }

        /// <summary>
        /// 详细表格报表
        /// </summary>
        /// <returns></returns>
        public List<object> ReportList()
        {
            DateTime NowTime = DateTime.Now;
            //1-12个月每日的财务报表集合
            List<object> FinanceList = new List<object>();
            //循环31日
            for (int i = 1; i <=31; i++)
            {
                Dictionary<string, object> dt = new Dictionary<string, object>()
                {
                    {"Day",i.ToString()+"日" },
                    {"January",GetReportInfo(i,1) },//MList应该是一个对象
                    {"February",GetReportInfo(i,2) },
                    {"March",GetReportInfo(i,3) },
                    {"April",GetReportInfo(i,4) },
                    {"May",GetReportInfo(i,5) },
                    {"June",GetReportInfo(i,6) },
                    {"July",GetReportInfo(i,7) },
                    {"August",GetReportInfo(i,8) },
                    {"September",GetReportInfo(i,9) },
                    {"October",GetReportInfo(i,10) },
                    {"November",GetReportInfo(i,11) },
                    {"December",GetReportInfo(i,12) }
                };
                FinanceList.Add(dt);
            }
            return FinanceList;
        }

        /// <summary>
        /// 根据日 月 获取收入支出信息
        /// </summary>
        /// <param name="D"></param>
        /// <param name="M"></param>
        /// <returns></returns>
        public string GetReportInfo(int D,int M)
        {
            double ct = 0;
            foreach (Finance_List f in db.Set<Finance_List>().Where(n=>n.StatusID==1))
            {
                if (f != null)
                {
                    int Y = Convert.ToDateTime(f.HandleDate.ToString().Split(' ')[0]).Year;
                    int FM = Convert.ToDateTime(f.HandleDate.ToString().Split(' ')[0]).Month;
                    int FD = Convert.ToDateTime(f.HandleDate.ToString().Split(' ')[0]).Day;
                    if (DateTime.Now.Year == Y & M == FM & D == FD)
                    {
                        ct += double.Parse(f.Amount.ToString());
                    }
                }
            }
            if (ct==0)
            {
                return "";
            }
            return ct.ToString();
        }
        #endregion
    }
}
